<?php


namespace app\core;


use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

use think\exception\ValidateException;
use think\facade\Filesystem;



class ExcelService
{
    // 导出
    public static function export($header = [], $expTableData = [], $fileName = "", $params=[], $isSave = "")
    {
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
		
        $expCellName=[];
        $tmp=[];
        if(isset($expTableData[0])){
            $tmp=$expTableData[0];
            foreach($header  as $item){
                if(array_key_exists($item[0],$tmp)){
                    $expCellName[]=$item;
                }
            }
        }else{
            $expCellName=$header;
        }


        $cellNum = count($expCellName);
        $dataNum = count($expTableData);

        $spreadsheet=new Spreadsheet();
		$sheet=$spreadsheet->getActiveSheet();
        
        $titleName=isset($params['titleName'])?$params['titleName']:'智慧熊';
		$t = isset($params['isPass11'])?$params['isPass11']:'';
        $invalidCharacters = $sheet->getInvalidCharacters();
        $titleName = str_replace($invalidCharacters, '', $titleName);
        $sheet->setTitle($titleName);
        $sheet->mergeCells('A1:' . $cellName[$cellNum - 1] . '1');//合并单元格
        $sheet->setCellValue('A1', $titleName);
        $sheet->getRowDimension('1')->setRowHeight(24);
        $sheet->getStyle('A1')->getFont()->setBold(true)->setName('黑体')->setSize(14);
        // 循环设置表头数据
        
        for ($i = 0; $i < $cellNum; $i++) {
            $sheet->setCellValue($cellName[$i] . '2', $expCellName[$i][1]);
        }
        $sheet->getRowDimension('2')->setRowHeight(18);
        $sheet->getStyle($cellName[0].'2:'.$cellName[$cellNum-1].'2')->getFont()->setBold(true)->setName('黑体')->setSize(12);//表头字体加粗
        $sheet->getStyle($cellName[0].'2:'.$cellName[$cellNum-1].'2')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('a0a0a0');
        // 循环数据
        for ($i = 0; $i < $dataNum; $i++) {
            for ($j = 0; $j < $cellNum; $j++) {
                $sheet->setCellValue($cellName[$j] . ($i + 3), $expTableData[$i][$expCellName[$j][0]].$t);
            }
            $sheet->getRowDimension(($i+3))->setRowHeight(18);
        }
        
        $sheet->getStyle($cellName[0].'3:'.$cellName[$cellNum-1].($dataNum+2))->getFont()->setName('宋体')->setSize(10);//内容字体

        $styleArray = [
            'alignment'=> [
                'horizontal'=> \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertival' =>\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                'wrapText' => true,
           ],
        ];

        $borderStyle = [
                     'borders' => [
                        'allBorders' => [
                            'borderStyle' =>  \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN //细边框
                        ]
                    ]
        ];

            

        // 样式设置
        $sheet->getDefaultColumnDimension()->setWidth(24);
        $sheet->getStyle($cellName[0].'1:'.$cellName[$cellNum-1].($dataNum+2))->applyFromArray($styleArray);//设置水平居中对齐
        $sheet->getStyle($cellName[0].'2:'.$cellName[$cellNum-1].($dataNum+2))->applyFromArray($borderStyle);//设置边框

        if(isset($params['xlspath'])){
            $writer= IOFactory::createWriter($spreadsheet,'Xls');
            return $writer->save($params['xlspath']);
        }

        if(empty($isSave)){
            header('Content-Type:application/vnd.ms-excel');
            header('Content-Disposition:attachment;filename='.$fileName.'.xls');
            header('Cache-Control:max-age=0');
            $writer= IOFactory::createWriter($spreadsheet,'Xls');
            $writer->save('php://output');
        }else{
            $savename = explode(',',$isSave);
            $url = './print/' . getComId() . '/' . $savename[0];
            if (!file_exists($url)) {
                mkdir($url, 0777, true);
            }
            $writer= IOFactory::createWriter($spreadsheet,'Xls');
            $writer->save($url . '/' . $savename[1] . '.xls');
        }
    }

    // 导出工资条
    public static function exportSalary($header = [], $expTableData = [], $fileName = "",$params=[])
    {
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
		
        $expCellName=[];
        $tmp=[];
        if(isset($expTableData[0])){
            $tmp=$expTableData[0];
            foreach($header  as $item){
                if(array_key_exists($item[0],$tmp)){
                    $expCellName[]=$item;
                }
            }
        }else{
            $expCellName=$header;
        }


        $cellNum = count($expCellName);
        $dataNum = count($expTableData);

        $spreadsheet=new Spreadsheet();
		$sheet=$spreadsheet->getActiveSheet();
        
        $titleName=isset($params['titleName'])?$params['titleName']:'智慧熊';
        $invalidCharacters = $sheet->getInvalidCharacters();
        $titleName = str_replace($invalidCharacters, '', $titleName);
        $sheet->setTitle($titleName);
        $sheet->mergeCells('A1:' . $cellName[$cellNum - 1] . '1');//合并单元格
        $sheet->setCellValue('A1', $titleName);
        $sheet->getRowDimension('1')->setRowHeight(24);
        $sheet->getStyle('A1')->getFont()->setBold(true)->setName('黑体')->setSize(14);
        // 循环设置表头数据
        
        
       // $sheet->getRowDimension('2')->setRowHeight(18);
       // $sheet->getStyle($cellName[0].'2:'.$cellName[$cellNum-1].'2')->getFont()->setBold(true)->setName('黑体')->setSize(12);//表头字体加粗
      //  $sheet->getStyle($cellName[0].'2:'.$cellName[$cellNum-1].'2')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('a0a0a0');
        
        $row=0;
        // 循环数据
        for ($i = 0; $i < $dataNum; $i++) {
            $row=$i*4;
            // 循环设置表头数据
            for ($k = 0; $k < $cellNum; $k++) {
                $sheet->setCellValue($cellName[$k] .  ($row + 2), $expCellName[$k][1]);
            }

            for ($j = 0; $j < $cellNum; $j++) {
                $sheet->setCellValue($cellName[$j] . ($row + 3), $expTableData[$i][$expCellName[$j][0]]);
            }

            $sheet->mergeCells('A'.($row + 4).':' . $cellName[$cellNum - 1] . ($row + 4));//合并单元格
            $sheet->mergeCells('A'.($row + 5).':' . $cellName[$cellNum - 1] . ($row + 5));//合并单元格

            $sheet->getRowDimension(($row+2))->setRowHeight(24);
            $sheet->getRowDimension(($row+3))->setRowHeight(24);
        }
        
        $sheet->getStyle($cellName[0].'2:'.$cellName[$cellNum-1].($dataNum*$row+2))->getFont()->setName('宋体')->setSize(12);//内容字体

        $styleArray = [
            'alignment'=> [
                'horizontal'=> \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertival' =>\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                'wrapText' => true,
           ],
           'borders' => [
                'allBorders' => [
                    'borderStyle' =>  \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN //细边框
                ]
            ]
        ];

        $borderStyle = [
                     'borders' => [
                        'allBorders' => [
                            'borderStyle' =>  \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN //细边框
                        ]
                    ]
        ];

       // echo $dataNum*$row;exit;

        // 样式设置
        $sheet->getDefaultColumnDimension()->setWidth(17);
        $sheet->getStyle($cellName[0].'1:'.$cellName[$cellNum-1].($row+4))->applyFromArray($styleArray);//设置水平居中对齐
        //$sheet->getStyle($cellName[0].'1:'.$cellName[$cellNum-1].($dataNum*$row+2))->applyFromArray($borderStyle);//设置边框

        header('Content-Type:application/vnd.ms-excel');
		header('Content-Disposition:attachment;filename='.$fileName.'.xls');
		header('Cache-Control:max-age=0');
		$writer= IOFactory::createWriter($spreadsheet,'Xls');
		$writer->save('php://output');
    }

    // excel导入
    public static function importExcel($filename = "")
    {
        $file[] = $filename;
        try {
            // 验证文件大小，名称等是否正确
            validate(['file' => 'filesize:51200|fileExt:xls,xlsx'])
                ->check($file);
            // 将文件保存到本地
            $savename = Filesystem::putFile('topic', $file[0]);
            // 截取后缀
            $fileExtendName = substr(strrchr($savename, '.'), 1);
            // 有Xls和Xlsx格式两种
            if ($fileExtendName == 'xlsx') {
                $objReader = IOFactory::createReader('Xlsx');
            } else {
                $objReader = IOFactory::createReader('Xls');
            }
            // 设置文件为只读
            $objReader->setReadDataOnly(TRUE);
            // 读取文件，tp6默认上传的文件，在runtime的相应目录下，可根据实际情况自己更改
            $objPHPExcel = $objReader->load(public_path() . 'storage/' . $savename);
            //excel中的第一张sheet
            $sheet = $objPHPExcel->getSheet(0);
            // 取得总行数
            $highestRow = $sheet->getHighestRow();
            // 取得总列数
            $highestColumn = $sheet->getHighestColumn();
            Coordinate::columnIndexFromString($highestColumn);
            $lines = $highestRow - 1;
            if ($lines <= 0) {
                echo('数据不能为空！');
                exit();
            }
            // 直接取出excle中的数据
            $data = $objPHPExcel->getActiveSheet()->toArray();
            // 删除第一个元素（表头）
            array_shift($data);
            // 返回结果
            return $data;
        } catch (ValidateException $e) {
            return $e->getMessage();
        }
    }

}